<?php
/**
 * This file contains the class AlertsDB which provides
 * all of the database requirements for the alerts page
 *
 * @copyright  2009 A Memory Tree
 * @license    A Memory Tree License
 */
/**
 * This class provides access to the database for alerts
 *
 * @copyright  2009 A Memory Tree
 * @license    A Memory Tree License
 */
class AlertsDB
{
    /**
      * Provides access to the database
     *
     * @var Db
     */
    private $_db;
    /**
     * Create a new instance of this class
     * 
     * @return void
     * @param Db &$db
     */
    function __construct(&$db)
    {
        $this->_db = $db;
    }
    /**
     * Escape the provided value
     *
     * @return string
     * @param mixed $value
     */
    function escape($value)
    {
        $db = $this->_db;
        return $db->escape($value);
    }
    /**
     * Get alerts for a user
     *
     * @return array[][string]string
     * @param int $userId
     */
    function getAlerts($userId)
    {
        $db = $this->_db;
        $alerts = array();
        $sql = <<< EOT
            SELECT Alerts.Id, FirstName, LastName, Purchased, Paid
            FROM Alerts
            WHERE
                Enabled = 1 AND
                UserId = %d
            
EOT;
        $sql = sprintf($sql, $userId);
        $result = $db->query($sql);
        if ($db->rowCount($result) > 0) {
            while (TRUE) {
                $row = $db->fetch($result);
                if (empty($row)) {
                    break;
                }
                $id = intval($row['Id']);
                $alert = array(
                    'firstName' => $db->unescape($row['FirstName']),
                    'lastName' => $db->unescape($row['LastName']),
                    'purchased' => $db->unescape($row['Purchased']),
                    'paid' => $db->unescape($row['Paid'])
                );
                $alerts[$id] = $alert;
            }
        }
        return $alerts;
    }
    /**
     * Check if the person has paid before
     *
     * @return bool
     * @param int $userId
     */
    function hasPaid($userId)
    {
        $db = $this->_db;
        $hasPaid = FALSE;
        $sql = <<< EOT
            SELECT Paid
            FROM Alerts
            WHERE
                Paid = 1 AND
                UserId = %d
EOT;
        $sql = sprintf($sql, $userId);
        $result = $db->query($sql);
        if ($db->rowCount($result) > 0) {
            $hasPaid = TRUE;
        }
        return $hasPaid;
    }
    /**
     * Check if the person has an existing corporate package
     *
     * @return bool
     * @param int $userId
     */
    function hasPackage($userId)
    {
        $db = $this->_db;
        $hasPackage = FALSE;
        $sql = <<< EOT
            SELECT Enabled
            FROM AlertsPackage
            WHERE
                Enabled = 1 AND
                adddate(Purchased, INTERVAL 1 YEAR) > now() AND
                UserId = %d
EOT;
        $sql = sprintf($sql, $userId);
        $result = $db->query($sql);
        if ($db->rowCount($result) > 0) {
            $hasPackage = TRUE;
        }
        return $hasPackage;
    }
    /**
     * Check if the person has an existing corporate package
     *
     * @return array[int][string]string
     * @param int $userId
     */
    function getPackages($userId)
    {
        $db = $this->_db;
        $packages = /*.(array[int][string]string).*/ array();
        $sql = <<< EOT
            SELECT TransId, Purchased, Remaining
            FROM AlertsPackage
            WHERE
                Enabled = 1 AND
                Remaining > 0 AND
                adddate(Purchased, INTERVAL 1 YEAR) > now() AND
                UserId = %d
EOT;
        $sql = sprintf($sql, $userId);
        $result = $db->query($sql);
        if ($db->rowCount($result) > 0) {
            while (TRUE) {
                $row = $db->fetch($result);
                if (empty($row)) {
                    break;
                }
                $package = /*.(array[string]string).*/ array();
                $package['transId'] = $row['TransId'];
                $package['purchased'] = $db->unescape($row['Purchased']);
                $package['remaining'] = $row['Remaining'];
                array_push($packages, $package);
            }
        }
        return $packages;
    }
    /**
     * Check if the person has any remaining credits
     *
     * @return bool
     * @param int $userId
     */
    function hasPackageRemaining($userId)
    {
        $db = $this->_db;
        $remaining = FALSE;
        $sql = <<< EOT
            SELECT TransId, Purchased, Remaining
            FROM AlertsPackage
            WHERE
                Enabled = 1 AND
                Remaining > 0 AND
                adddate(Purchased, INTERVAL 1 YEAR) > now() AND
                UserId = %d
EOT;
        $sql = sprintf($sql, $userId);
        $result = $db->query($sql);
        if ($db->rowCount($result) > 0) {
            $remaining = TRUE;
        }
        return $remaining;
    }
    /**
     * Remove a credit from the package
     *
     * @return bool
     * @param int $userId
     */
    function decreaseRemainingPackage($userId)
    {
        $db = $this->_db;
        $success = FALSE;
        $sql = <<< EOT
            UPDATE AlertsPackage
                SET
                    Remaining = Remaining - 1
            WHERE
                Enabled = 1 AND
                Remaining > 0 AND
                adddate(Purchased, INTERVAL 1 YEAR) > now() AND
                UserId = %d
EOT;
        $sql = sprintf($sql, $userId);
        $result = $db->query($sql);
        return $success === TRUE;
    }
    /**
     * Add a paid alert for a person
     *
     * @return bool
     * @param int $userId
     * @param string $firstName
     * @param string $lastName
     */
    function addPaidAlert($userId, $lastName, $firstName)
    {
        $db = $this->_db;
        $sql = <<< EOT
            INSERT INTO
                Alerts (UserId, LastName, FirstName, Paid)
                VALUES (%d, '%s', '%s', TRUE)
EOT;
        $sql = sprintf($sql,
            $userId,
            $db->escape($lastName),
            $db->escape($firstName)
        );
        $success = $db->query($sql);
        return $success === TRUE;
    }
    /**
     * Add an unpaid alert for a person
     *
     * @return bool
     * @param int $userId
     * @param string $firstName
     * @param string $lastName
     */
    function addUnpaidAlert($userId, $lastName, $firstName)
    {
        $db = $this->_db;
        $sql = <<< EOT
            INSERT INTO
                Alerts (UserId, LastName, FirstName, Paid)
                VALUES (%d, '%s', '%s', FALSE)
EOT;
        $sql = sprintf($sql,
            $userId,
            $db->escape($lastName),
            $db->escape($firstName)
        );
        $success = $db->query($sql);
        return $success === TRUE;
    }
    /**
     * Add an alert
     *
     * @return bool
     * @param int $alertId
     * @param string $firstName
     * @param string $lastName
     */
    function saveFullName($alertId, $firstName, $lastName)
    {
        $db = $this->_db;
        $sql = <<< EOT
            UPDATE Alerts
                SET
                    FirstName = '%s',
                    LastName = '%s'
            WHERE Id = %d
EOT;
        $sql = sprintf($sql, $firstName, $lastName, $alertId);
        $success = $db->query($sql);
        return $success === TRUE;
    }
    /**
     * Add an alert
     *
     * @return bool
     * @param int $alertId
     * @param string $firstName
     */
    function saveFirstName($alertId, $firstName)
    {
        $db = $this->_db;
        $sql = <<< EOT
            UPDATE Alerts
                SET FirstName = '%s'
            WHERE Id = %d
EOT;
        $sql = sprintf($sql, $firstName, $alertId);
        $success = $db->query($sql);
        return $success === TRUE;
    }
    /**
     * Disable an alert
     *
     * @return bool
     * @param int $alertId
     */
    function disableAlert($alertId)
    {
        $db = $this->_db;
        $sql = <<< EOT
            UPDATE Alerts
                SET Enabled = 0
            WHERE Id = %d
EOT;
        $sql = sprintf($sql, $alertId);
        $success = $db->query($sql);
        return $success === TRUE;
    }
}
?>